﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Newtonsoft.Json;
using WS.EKA.FJYCM.Business.DAL;
using System.Data;
using WS.EKA.FJYCM.Business.Model;
using WS.EKA.FJYCM.Utilities;

namespace WS.EKA.FJYCM.Business
{
    public class IncidentAccess
    {
        static IDB db = DBFactory.CreatePDB();

        public static string GetMonthlyCategoryPercentJson(int preMonths)
        {
            string sql = @"Select T.Category, Count(T.Id) Total From Incident T where T.Createtime>=  add_months(sysdate,-"+preMonths.ToString()+") group by T.Category";

            var table = db.ExecuteDataTable(sql);

            List<string> list = new List<string>();

            foreach (DataRow row in table.Rows)
            {
                list.Add(string.Format("['{0}',{1}]", row["Category"], row["Total"]));
            }
            StringBuilder json = new StringBuilder();
            json.Append("[");
            json.Append(string.Join(",", list));
            json.Append("]");
            return json.ToString();
        }

        public static List<DeviceTypePercent> GetMonthlyDeviceTypePercentList(int preMonths)
        {
            string sql = @"Select A.Type,Count(B.Id) Total From Device A left Join Alert B On A.Id=B.Deviceid and  B.Createtime>=  add_months(sysdate,-"+preMonths.ToString()+") group by A.Type";

            var table = db.ExecuteDataTable(sql);

            List<DeviceTypePercent> list = table.ToList<DeviceTypePercent>();

            decimal sum = list.Sum(t => t.Total);

            list.ForEach(t => t.Percent = (t.Total / sum).ToString("P1"));

            return list;
            //foreach (DataRow row in table.Rows)
            //{
            //    list.Add(string.Format("['{0}',{1}]", row["Type"], row["Total"]));
            //}
            //StringBuilder json = new StringBuilder();
            //json.Append("[");
            //json.Append(string.Join(",", list));
            //json.Append("]");
            //return json.ToString();
        }

        /// <summary>
        /// 月度省局事件解决效率
        /// </summary>
        /// <returns></returns>
        public static string GetMonthlyProvincialEfficiencyPercentJson()
        {
            StringBuilder json = new StringBuilder();
            json.Append("[");
            List<string> list = new List<string>();

            list.Add(getProvincialEfficiencyByMinutes("5分钟以内", "minutes <= 5"));
            list.Add(getProvincialEfficiencyByMinutes("5-30分钟", "minutes > 5 and minutes <= 30"));
            //list.Add(getProvincialEfficiencyByMinutes("10-30分钟", "minutes > 10 and minutes <= 30"));
            list.Add(getProvincialEfficiencyByMinutes("0.5-2小时", "minutes > 30 and minutes <= 120"));
            //list.Add(getProvincialEfficiencyByMinutes("1-2小时", "minutes > 60 and minutes <= 120"));
            list.Add(getProvincialEfficiencyByMinutes("2-8小时", "minutes > 120 and minutes <= 480"));
            //list.Add(getProvincialEfficiencyByMinutes("4-8小时", "minutes > 240 and minutes <= 480"));
            list.Add(getProvincialEfficiencyByMinutes("8-24小时", "minutes > 480 and minutes <= 1440"));
            list.Add(getProvincialEfficiencyByMinutes("1-5天", "minutes > 1440 and minutes <= 7200"));
            //list.Add(getProvincialEfficiencyByMinutes("2-5天", "minutes > 2880 and minutes <= 7200"));
            list.Add(getProvincialEfficiencyByMinutes("5-30天", "minutes > 7200 and minutes <= 43200"));
            //list.Add(getProvincialEfficiencyByMinutes("10-30天", "minutes > 14400 and minutes <= 43200"));
            list.Add(getProvincialEfficiencyByMinutes("30天以上", "minutes > 43200"));

            json.Append(string.Join(",", list));
            json.Append("]");
            return json.ToString();
        }

        /// <summary>
        /// 根据时间段读取事件解决效率
        /// </summary>
        /// <param name="category"></param>
        /// <param name="andCondition"></param>
        /// <returns></returns>
        private static string getProvincialEfficiencyByMinutes(string category, string andCondition)
        {
            var rdb = DBFactory.CreateARDB();

            string sql = "select count(*) total from Incident_Resolve_Chart_View where createtime > add_months(sysdate,-1) and company='福建省烟草专卖局' and " + andCondition;

            var table = rdb.ExecuteDataTable(sql);

            return string.Format("['{0}',{1}]", category, table.Rows[0]["Total"]);
        }

        /// <summary>
        /// 年度省局运维人员解决事件效率
        /// </summary>
        /// <returns></returns>
        public static string GetYearProvincialEfficiencyPercentJson()
        {
            var rdb = DBFactory.CreateARDB();

            string sql = "select assignee,count(assignee) Total from Incident_Resolve_Chart_View where assigneedepart='省局机关运维' and resolvetime >= trunc(sysdate,'YEAR') group by assignee order by count(assignee) desc ";

            var table = rdb.ExecuteDataTable(sql);

            List<string> list = new List<string>();

            foreach (DataRow row in table.Rows)
            {
                list.Add(string.Format("['{0}',{1}]", row["assignee"], row["Total"]));
            }
            StringBuilder json = new StringBuilder();
            json.Append("[");
            json.Append(string.Join(",", list));
            json.Append("]");
            return json.ToString();
        }
    }
}
